﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.Common;

namespace System.Data
{
    /// <summary>
    /// 数据库辅助类的基类
    /// </summary>
    public abstract class BaseSQLHelper
    {
        /// <summary>
        /// 主要的链接
        /// </summary>
        protected IDbConnection _Connection;

        #region 辅助记录日志的属性

        /// <summary>
        /// 数据库名称
        /// </summary>
        protected string _Name = "NoName";

        /// <summary>
        /// 数据据路径
        /// </summary>
        protected string _Path = "NoPath";

        #endregion 辅助记录日志的属性

        #region 公开的属性

        /// <summary>
        /// 是否可以用
        /// </summary>
        public bool IsConnect
        {
            get
            {
                return _CheckConnect();
            }
        }

        /// <summary>
        /// 数据库名称
        /// </summary>
        public string Name
        {
            get
            {
                return _Name;
            }
        }

        /// <summary>
        /// 数据库路径
        /// </summary>
        public string Path
        {
            get
            {
                return _Path;
            }
        }

        #endregion 公开的属性

        /// <summary>
        /// 获取一个SQL命令对象
        /// </summary>
        /// <returns></returns>
        protected virtual IDbCommand _GetCommand()
        {
            return _Connection.CreateCommand();
        }

        #region 公开给外部调用的功能性接口

        /// <summary>
        /// 默认的初始化和类构造
        /// </summary>
        /// <param name="connectCmd"></param>
        /// <param name="autoOpen"></param>
        public BaseSQLHelper(string connectCmd, bool autoOpen = true)
        {
            _Path = connectCmd;
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataSetting"></param>
        public bool CreateTable(string tableName, string dataSetting)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _CreateTable(tableName, dataSetting);
        }

        /// <summary>
        /// 根据类型创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ty"></param>
        /// <param name="newDbTypeConvert"></param>
        public bool CreateTable(string tableName, Type ty,IDictionary<Type,string> newDbTypeConvert = null)
        {
            if (!IsConnect)
            {
                return false;
            }

            string[] lines = ty.GetCreateTableSQLCmd(newDbTypeConvert);
            string dataSetting = "";
            string connetSymbol = ",\n";

            foreach(string line in lines)
            {
                if(string.IsNullOrEmpty(line) || string.IsNullOrWhiteSpace(line))
                {
                    continue;
                }

                dataSetting += line + connetSymbol;
            }

            // 直接去除最后一个符号
            dataSetting = dataSetting.Remove(dataSetting.Length - connetSymbol.Length, connetSymbol.Length);
            return _CreateTable(tableName, dataSetting);
        }

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="cmd"></param>
        public bool DeleteTable(string tableName, string cmd = "DROP")
        {
            if (!IsConnect)
            {
                return false;
            }

            return _DeleteTable(tableName, cmd);
        }

        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="cmd"></param>
        public bool ClearTable(string tableName, string cmd = "TRUNCATE")
        {
            if (!IsConnect)
            {
                return false;
            }

            return _DeleteTable(tableName, cmd);
        }

        /// <summary>
        /// 向表中添加单行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dict"></param>
        /// <param name="checkExistsCondition"></param>
        /// <returns></returns>
        public bool InsertToTable(string tableName, IDictionary dict,string checkExistsCondition = null)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _InsertOneRowToTable(tableName, dict, checkExistsCondition);
        }

        /// <summary>
        /// 向表中添加多行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dicts"></param>
        /// <returns></returns>
        public bool InsertToTable(string tableName, IDictionary[] dicts)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _InsertToTable(tableName, dicts);
        }

        /// <summary>
        /// 只获取单行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        public IDictionary<string,object> GetOneRow(string tableName,string condition,string cols ="*")
        {
            if (!IsConnect)
            {
                return null;
            }

            return _GetOneRow(tableName, condition,cols);
        }

        /// <summary>
        /// 获取多行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        public IDictionary<string, object>[] GetRows(string tableName, string condition, string cols = "*")
        {
            if (!IsConnect)
            {
                return null;
            }

            return _GetRows(tableName, condition, cols);
        }


        /// <summary>
        /// 更新表中的单行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dict"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public bool UpdateRow(string tableName, IDictionary dict,string condition)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _UpdateOneRow(tableName, dict, condition);
        }

        /// <summary>
        /// 更新表中的多行数据
        /// <para>字典对象的数量一定要和天骄数量相同</para>
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dicts"></param>
        /// <param name="conditionColumn"></param>
        /// <returns></returns>
        public bool UpdateRow(string tableName, IDictionary[] dicts, string conditionColumn)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _UpdateRow(tableName, dicts, conditionColumn);
        }

        /// <summary>
        /// 删除指定行
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public bool DeleteRow(string tableName,string condition)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _DeleteRow(tableName, condition);
        }

        /// <summary>
        /// 直接执行SQL指令，只返回成功与否
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <returns></returns>
        public bool ExcuteCmd(string sqlcmd)
        {
            if (!IsConnect)
            {
                return false;
            }

            return _ExcuteCmd(sqlcmd);
        }

        #endregion 公开给外部调用的功能性接口

        #region 管理实际业务的主要逻辑 -- 子类需要重点关注即重写的主要方法

        /// <summary>
        /// 检测状态和合法性
        /// </summary>
        /// <returns></returns>
        protected virtual bool _CheckConnect()
        {
            if (_Connection.State != ConnectionState.Open)
            {
                _LogError($" {_Name}({GetType().Name}) Not Connect!");
                return false;
            }

            return true;
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dataSetting"></param>
        protected virtual bool _CreateTable(string tableName, string dataSetting)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    string keys = !string.IsNullOrEmpty(dataSetting) ? $"({dataSetting})" : "(id int)";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = $"CREATE TABLE {tableName} {keys};";
                    int r = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._CreateTable:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// 删除整张表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="delcmd"></param>
        protected virtual bool _DeleteTable(string tableName, string delcmd)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = $"{delcmd} TABLE {tableName};";
                    int r = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._DeleteTable:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// 只获取一行的数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        protected virtual IDictionary<string, object> _GetOneRow(string tableName, string condition, string cols)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                IDataReader reader = default(IDataReader);
                try
                {
                    string sqlcmd = $"SELECT {cols} FROM {tableName} WHERE {condition};";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;

                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        Dictionary<string, object> result = new Dictionary<string, object>();
                        int count = reader.FieldCount;
                        for (int i = 0; i < count; i++)
                        {
                            result.Add(reader.GetName(i), reader.GetValue(i));
                        }
                        return result;
                    }

                    return null;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._GetOneRow:{e.Message}";
                    _LogError(log);
                    return null;
                }
                finally
                {
                    cmd.Dispose();
                    if (reader != null)
                    {
                        reader.Close();
                        reader = default(IDataReader);
                    }
                }
            }
        }

        /// <summary>
        /// 读取多行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        protected virtual IDictionary<string, object>[] _GetRows(string tableName, string condition, string cols)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                IDataReader reader = default(IDataReader);
                try
                {
                    string sqlcmd = $"SELECT {cols} FROM {tableName} " +
                        (string.IsNullOrEmpty(condition) ? ";" : $" WHERE {condition};");

                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;

                    reader = cmd.ExecuteReader();

                    if ((reader as DbDataReader).HasRows)
                    {
                        int count = reader.FieldCount;
                        string[] keys = new string[count];
                        for (int i = 0; i < count; i++)
                        {
                            keys[i] = reader.GetName(i);
                        }

                        List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
                        while (reader.Read())
                        {
                            Dictionary<string, object> dict = SQLUtilies.CreateRowDataKeys<string, object>(keys);
                            for (int i = 0; i < count; i++)
                            {
                                dict[keys[i]] = reader.GetValue(i);
                            }
                            result.Add(dict);
                        }

                        return result.ToArray();
                    }

                    return null;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._GetRows:{e.Message}";
                    _LogError(log);
                    return null;
                }
                finally
                {
                    cmd.Dispose();
                    if (reader != null)
                    {
                        reader.Close();
                        reader = default(IDataReader);
                    }
                }
            }
        }

        /// <summary>
        /// 获取整张表的数据（不设条件）
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="cols"></param>
        /// <returns></returns>
        protected virtual IDictionary<string, object>[] _GetTable(string tableName, string cols)
        {
            return null;
        }


        /// <summary>
        /// 向表中添加数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dict"></param>
        /// <param name="checkExistsCondition"></param>
        /// <returns></returns>
        protected virtual bool _InsertOneRowToTable(string tableName, IDictionary dict, string checkExistsCondition)
        {
            // 准备 sql 语句
            string keys = dict.GetKeys();
            string values = dict.GetValues();

            string sqlcmd = $"INSERT INTO {tableName} ({keys}) VALUES ({values});";


            if (!string.IsNullOrEmpty(checkExistsCondition) && !string.IsNullOrWhiteSpace(checkExistsCondition))
            {
                sqlcmd = $@"
                INSERT INTO {tableName} ({keys})
                SELECT {values}
                FROM {tableName} WHERE NOT EXISTS
                (SELECT {keys}
                FROM {tableName} WHERE {checkExistsCondition});
                            ";
            }

            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;
                    int r = cmd.ExecuteNonQuery();
                    return r > 0;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._InsertOneRowToTable:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                    sqlcmd = null;
                    keys = null;
                    values = null;
                }
            }
        }

        /// <summary>
        /// 向表中添加多行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dicts"></param>
        /// <returns></returns>
        protected virtual bool _InsertToTable(string tableName, IDictionary[] dicts)
        {
            return true;
        }

        /// <summary>
        /// 更新表中的单行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dict"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        protected virtual bool _UpdateOneRow(string tableName, IDictionary dict, string condition)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    string keys = dict.GetSQLUpdateSet();

                    string sqlcmd = $"UPDATE {tableName} SET {keys} WHERE {condition};";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;
                    int r = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._UpdateOneRow:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// 更新表中的多行数据
        /// <para>字典对象的数量一定要和条件数量相同</para>
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dicts"></param>
        /// <param name="conditionColumn"></param>
        /// <returns></returns>
        protected virtual bool _UpdateRow(string tableName, IDictionary[] dicts, string conditionColumn)
        {
            string spaceChar = ",";
            string[] keys = dicts[0].GetKeys().Split(',');
            string r = "";
            string conditions = "";
            foreach (string key in keys)
            {
                // 跳过作为条件的列
                if (key == conditionColumn)
                {
                    continue;
                }

                r += $"{key} = CASE {conditionColumn} ";

                for (int i = 0; i < dicts.Length; i++)
                {
                    IDictionary dict = dicts[i];

                    object value = dict[key];

                    //有可能出现为空的情况
                    //conditionColumn 是 string ，要注意
                    if (value == null || !dict.Contains(conditionColumn) || !dict.Contains(key) ||
                        string.IsNullOrEmpty(value.ToString()) || string.IsNullOrWhiteSpace(value.ToString()))
                    {
                        continue;
                    }
                    r += $"WHEN {dict[conditionColumn]} THEN '{dict[key]}' ";
                    conditions += $"{dict[conditionColumn]}{spaceChar}";
                }

                r += $"END {spaceChar}";
            }

            // 直接去除最后一个符号
            r = r.Remove(r.Length - spaceChar.Length, spaceChar.Length);
            conditions = conditions.Remove(conditions.Length - spaceChar.Length, spaceChar.Length);

            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    string sqlcmd = $"UPDATE {tableName} SET {r} WHERE {conditionColumn} IN ({conditions});";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;
                    int rn = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._UpdateOneRow:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                    r = null;
                    conditions = null;
                }
            }
        }


        /// <summary>
        /// 删除指定行
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        protected virtual bool _DeleteRow(string tableName, string condition)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    string sqlcmd = $"DELETE FROM {tableName} WHERE {condition};";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;
                    int r = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._DeleteRow:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                }
            }
        }

        /// <summary>
        /// 直接执行SQL指令，只返回成功与否
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <returns></returns>
        protected virtual bool _ExcuteCmd(string sqlcmd)
        {
            using (IDbCommand cmd = _Connection.CreateCommand())
            {
                try
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = sqlcmd;
                    int r = cmd.ExecuteNonQuery();
                    return true;
                }
                catch (Exception e)
                {
                    string log = $"{_Name}({GetType()})._ExcuteCmd:{e.Message}";
                    _LogError(log);
                    return false;
                }
                finally
                {
                    cmd.Dispose();
                }
            }
        }

        #endregion 管理实际业务的主要逻辑

        #region 异常处理

        /// <summary>
        /// 信息处理
        /// <para>默认信息处理是输出到控制台</para>
        /// </summary>
        /// <param name="info"></param>
        protected virtual void _LogError(string info)
        {
            Console.WriteLine(info);
        }

        #endregion 异常处理
    }
}
